create  table jms_dm.dm_terminal_sign_summary_plan_sign_network_dt
(
final_sign_network_code varchar(100) COMMENT '最终签收派件网点编码' ,
end_franchisee_code varchar(100) COMMENT '末端网点所属加盟商编码' ,
end_agent_code varchar(100) COMMENT '末端网点所属代理区编码' ,
end_manage_region_code varchar(100) COMMENT '末端网点所属管理大区编码' ,
end_manage_region_name varchar(100) COMMENT '末端网点所属管理大区名称' ,

end_agent_name varchar(100) COMMENT '末端网点所属代理区名称' ,
end_provider_id varchar(100) COMMENT '末端网点所属省份id' ,
end_provider_name varchar(100) COMMENT '末端网点所属省份名称' ,
end_city_id varchar(100) COMMENT '末端网点所属城市id' ,
end_city_name varchar(100) COMMENT '末端网点所属城市名称' ,
end_area_id varchar(100) COMMENT '末端网点所属区县id' ,
end_area_name varchar(100) COMMENT '末端网点所属区县名称' ,
 end_center_code varchar(100) COMMENT '末端发件中心/集散编码' ,
end_center_name varchar(100) COMMENT '末端发件中心/集散名称' ,

end_franchisee_name varchar(100) COMMENT '末端网点所属加盟商名称' ,

final_sign_network_name varchar(100) COMMENT '最终签收派件网点名称' ,
final_sign_user_name varchar(100) COMMENT '最后签收派件员名称' ,
need_sign_count int(10) COMMENT '应签收汇总' ,
aging_sign_in_time_network int(10) COMMENT '时效签收准点网点签收' ,
aging_sign_in_time_terminal_pdd int(10) COMMENT '时效签收准点终端入库(桃花岛认证)' ,
aging_sign_in_time_terminal_other int(10) COMMENT '时效签收准点终端入库(其他)' ,
aging_sign_in_time_proxy int(10) COMMENT '时效签收准点代理点收入' ,
aging_sign_in_time_transfer int(10) COMMENT '时效签收准点转邮' ,
aging_sign_in_time_count int(10) COMMENT '时效签收准点汇总' ,

22_aging_sign_over_time_network int(10) COMMENT '时效签收延误网点签收' ,
22_aging_sign_over_time_terminal_pdd int(10) COMMENT '时效签收延误终端入库(桃花岛认证)' ,
22_aging_sign_over_time_terminal_other int(10) COMMENT '时效签收延误终端入库(其他)' ,
22_aging_sign_over_time_proxy int(10) COMMENT '时效签收延误代理点收入' ,
22_aging_sign_over_time_difficult int(10) COMMENT '时效签收延误问题件' ,
22_aging_sign_over_time_other int(10) COMMENT '时效签收延误其他' ,
22_aging_sign_over_time_transfer int(10) COMMENT '时效签收延误转邮' ,
22_aging_sign_over_time_sign_count int(10) COMMENT '时效签收延误已签收汇总' ,
22_aging_sign_over_time_nosign_count int(10) COMMENT '时效签收延误未签收汇总' ,

24_aging_sign_over_time_network int(10) COMMENT '时效签收延误网点签收' ,
24_aging_sign_over_time_terminal_pdd int(10) COMMENT '时效签收延误终端入库(桃花岛认证)' ,
24_aging_sign_over_time_terminal_other int(10) COMMENT '时效签收延误终端入库(其他)' ,
24_aging_sign_over_time_proxy int(10) COMMENT '时效签收延误代理点收入' ,
24_aging_sign_over_time_difficult int(10) COMMENT '时效签收延误问题件' ,
24_aging_sign_over_time_other int(10) COMMENT '时效签收延误其他' ,
24_aging_sign_over_time_transfer int(10) COMMENT '时效签收延误转邮' ,
24_aging_sign_over_time_sign_count int(10) COMMENT '时效签收延误已签收汇总' ,
24_aging_sign_over_time_nosign_count int(10) COMMENT '时效签收延误未签收汇总' ,

00_aging_sign_over_time_network int(10) COMMENT '时效签收延误网点签收' ,
00_aging_sign_over_time_terminal_pdd int(10) COMMENT '时效签收延误终端入库(桃花岛认证)' ,
00_aging_sign_over_time_terminal_other int(10) COMMENT '时效签收延误终端入库(其他)' ,
00_aging_sign_over_time_proxy int(10) COMMENT '时效签收延误代理点收入' ,
00_aging_sign_over_time_difficult int(10) COMMENT '时效签收延误问题件' ,
00_aging_sign_over_time_other int(10) COMMENT '时效签收延误其他' ,
00_aging_sign_over_time_transfer int(10) COMMENT '时效签收延误转邮' ,
00_aging_sign_over_time_sign_count int(10) COMMENT '时效签收延误已签收汇总' ,
00_aging_sign_over_time_nosign_count int(10) COMMENT '时效签收延误未签收汇总' ,

aging_sign_24_count int(10) COMMENT '时效签收24点前签收汇总' ,
actual_sign_in_time_network int(10) COMMENT '实际签收准点网点签收' ,
actual_sign_in_time_terminal int(10) COMMENT '实际签收准点驿站/快递柜' ,
actual_sign_in_time_proxy int(10) COMMENT '实际签收准点代理点收入' ,
actual_sign_in_time_count int(10) COMMENT '实际签收准点汇总' ,
actual_sign_over_time_network int(10) COMMENT '实际签收延误网点签收' ,
actual_sign_over_time_terminal int(10) COMMENT '实际签收延误驿站/快递柜' ,
actual_sign_over_time_proxy int(10) COMMENT '实际签收延误代理点收入' ,
actual_sign_over_time_difficult int(10) COMMENT '实际签收延误问题件' ,
actual_sign_over_time_other int(10) COMMENT '实际签收延误其他' ,
actual_sign_over_time_sign_count int(10) COMMENT '实际签收延误已签收汇总' ,
actual_sign_over_time_nosign_count int(10) COMMENT '实际签收延误未签收汇总' ,
actual_sign_24_count int(10) COMMENT '实际签收24点前签收汇总' ,
final_plan_sign_date date COMMENT '规划签收日期' ,
aging_sign_00_count  int(10) COMMENT '时效签收24点前签收汇总' ,
aging_sign_22_count  int(10) COMMENT '时效签收24点前签收汇总' ,
is_common_network int(4) COMMENT '是否共配网点' ,

22_actual_sign_in_time_network  int(10) COMMENT '22点前实际签收准点网点签收' ,
22_actual_sign_in_time_terminal  int(10) COMMENT '22点前实际签收准点驿站/快递柜' ,
22_actual_sign_in_time_proxy  int(10) COMMENT '22点前实际签收准点代理点收入' ,
22_actual_sign_in_time_count  int(10) COMMENT '22点前实际签收准点汇总' ,
22_actual_sign_over_time_network  int(10) COMMENT '22点前实际签收延误网点签收' ,
22_actual_sign_over_time_terminal  int(10) COMMENT '22点前实际签收延误驿站/快递柜' ,
22_actual_sign_over_time_proxy  int(10) COMMENT '22点前实际签收延误代理点收入' ,
22_actual_sign_over_time_difficult  int(10) COMMENT '22点前实际签收延误问题件' ,
22_actual_sign_over_time_other  int(10) COMMENT '22点前实际签收延误其他' ,
22_actual_sign_over_time_sign_count  int(10) COMMENT '22点前实际签收延误已签收汇总' ,
22_actual_sign_over_time_nosign_count  int(10) COMMENT '22点前实际签收延误未签收汇总' ,

24_actual_sign_in_time_network  int(10) COMMENT '2224点实际签收准点网点签收' ,
24_actual_sign_in_time_terminal  int(10) COMMENT '2224点实际签收准点驿站/快递柜' ,
24_actual_sign_in_time_proxy  int(10) COMMENT '2224点实际签收准点代理点收入' ,
24_actual_sign_in_time_count  int(10) COMMENT '2224点实际签收准点汇总' ,
24_actual_sign_over_time_network  int(10) COMMENT '2224点实际签收延误网点签收' ,
24_actual_sign_over_time_terminal  int(10) COMMENT '2224点实际签收延误驿站/快递柜' ,
24_actual_sign_over_time_proxy  int(10) COMMENT '2224点实际签收延误代理点收入' ,
24_actual_sign_over_time_difficult  int(10) COMMENT '2224点实际签收延误问题件' ,
24_actual_sign_over_time_other  int(10) COMMENT '2224点实际签收延误其他' ,
24_actual_sign_over_time_sign_count int(10) COMMENT '2224点实际签收延误已签收汇总' ,
24_actual_sign_over_time_nosign_count int(10) COMMENT '2224实际签收延误未签收汇总' ,

00_actual_sign_in_time_network  int(10) COMMENT '超24点实际签收准点网点签收' ,
00_actual_sign_in_time_terminal int(10) COMMENT '超24点实际签收准点驿站/快递柜' ,
00_actual_sign_in_time_proxy  int(10) COMMENT '超24点实际签收准点代理点收入' ,
00_actual_sign_in_time_count  int(10) COMMENT '超24点实际签收准点汇总' ,
00_actual_sign_over_time_network  int(10) COMMENT '超24点实际签收延误网点签收' ,
00_actual_sign_over_time_terminal  int(10) COMMENT '超24点实际签收延误驿站/快递柜' ,
00_actual_sign_over_time_proxy  int(10) COMMENT '超24点实际签收延误代理点收入' ,
00_actual_sign_over_time_difficult  int(10) COMMENT '超24点实际签收延误问题件' ,
00_actual_sign_over_time_other  int(10) COMMENT '超24点实际签收延误其他' ,
00_actual_sign_over_time_sign_count int(10) COMMENT '超24点实际签收延误已签收汇总' ,
00_actual_sign_over_time_nosign_count int(10) COMMENT '超24点实际签收延误未签收汇总' ,
actual_sign_22_count  int(10) COMMENT '22点签收量' ,
actual_sign_00_count  int(10) COMMENT '24点前前签收量'
 )
ENGINE=OLAP
DUPLICATE KEY(final_sign_network_code,end_franchisee_code,end_agent_code)
comment '末端时效规划签收网点维度汇总'
PARTITION BY RANGE (final_plan_sign_date) (
   START ("2023-02-27") END ("2023-05-26") EVERY (INTERVAL 1 day))DISTRIBUTED BY HASH(final_sign_network_code) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-365",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);

alter table jms_dm.dm_terminal_sign_summary_plan_sign_network_dt
add column (
         order_source_name  varchar(100) comment'订单来源'
    );